const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

console.log('🔍 实例表字段映射准确性总结报告');
console.log('===================================');

try {
  const db = new Database(DB_PATH);
  
  // 1. 获取核心映射信息
  console.log('\n📊 核心映射逻辑分析...');
  
  const coreTables = ['table_c2', 'table_c4', 'table_c6'];
  const coreFieldCounts = {};
  
  for (const tableName of coreTables) {
    try {
      const fields = db.prepare(`PRAGMA table_info([${tableName}])`).all();
      const dataFields = fields.filter(f => !['id', 'created_at', 'updated_at'].includes(f.name));
      coreFieldCounts[tableName] = dataFields.length;
      
      console.log(`📋 ${tableName}: ${dataFields.length} 个数据字段`);
      if (dataFields.length <= 5) {
        dataFields.forEach(field => console.log(`  - ${field.name} (${field.type})`));
      } else {
        dataFields.slice(0, 3).forEach(field => console.log(`  - ${field.name} (${field.type})`));
        console.log(`  ... 还有 ${dataFields.length - 3} 个字段`);
      }
    } catch (error) {
      console.log(`❌ 无法读取 ${tableName}: ${error.message}`);
      coreFieldCounts[tableName] = 0;
    }
  }
  
  // 2. 获取所有实例表并分析字段结构
  console.log('\n🔍 实例表字段结构分析...');
  
  const instanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📋 实例表总数: ${instanceTables.length} 个`);
  
  let totalNamingFields = 0;
  let totalPositionFields = 0;
  let totalBaseParamsFields = 0;
  let totalSystemFields = 0;
  
  const fieldAnalysis = [];
  
  for (const table of instanceTables) {
    try {
      const fields = db.prepare(`PRAGMA table_info([${table.name}])`).all();
      
      const systemFields = fields.filter(f => 
        ['id', 'device_type_code', 'instance_number', 'object_number', 'object_description', 'created_at', 'updated_at'].includes(f.name)
      );
      const namingFields = fields.filter(f => f.name.startsWith('naming_'));
      const positionFields = fields.filter(f => f.name.startsWith('position_'));
      const baseParamsFields = fields.filter(f => f.name.startsWith('base_params_'));
      
      totalSystemFields += systemFields.length;
      totalNamingFields += namingFields.length;
      totalPositionFields += positionFields.length;
      totalBaseParamsFields += baseParamsFields.length;
      
      fieldAnalysis.push({
        tableName: table.name,
        totalFields: fields.length,
        systemFields: systemFields.length,
        namingFields: namingFields.length,
        positionFields: positionFields.length,
        baseParamsFields: baseParamsFields.length,
        hasCorrectNaming: namingFields.length === coreFieldCounts['table_c2'],
        hasPositionFields: positionFields.length > 0
      });
      
    } catch (error) {
      console.log(`❌ 分析 ${table.name} 失败: ${error.message}`);
    }
  }
  
  // 3. 映射逻辑验证
  console.log('\n🔧 映射逻辑验证结果:');
  console.log('======================');
  
  // 检查命名字段映射准确性
  const correctNamingTables = fieldAnalysis.filter(t => t.hasCorrectNaming);
  const incorrectNamingTables = fieldAnalysis.filter(t => !t.hasCorrectNaming);
  
  console.log(`✅ 命名字段映射正确: ${correctNamingTables.length}/${instanceTables.length} 个表`);
  console.log(`❌ 命名字段映射错误: ${incorrectNamingTables.length} 个表`);
  
  if (incorrectNamingTables.length > 0 && incorrectNamingTables.length <= 5) {
    console.log(`❌ 命名字段映射错误的表:`);
    incorrectNamingTables.forEach(t => {
      console.log(`  - ${t.tableName}: 期望${coreFieldCounts['table_c2']}个，实际${t.namingFields}个命名字段`);
    });
  }
  
  // 检查位置字段
  const tablesWithPosition = fieldAnalysis.filter(t => t.hasPositionFields);
  const tablesWithoutPosition = fieldAnalysis.filter(t => !t.hasPositionFields);
  
  console.log(`📍 有位置字段的表: ${tablesWithPosition.length} 个`);
  console.log(`📍 无位置字段的表: ${tablesWithoutPosition.length} 个`);
  
  // 4. 字段统计总结
  console.log('\n📊 字段统计总结:');
  console.log('================');
  
  const avgFieldsPerTable = (totalSystemFields + totalNamingFields + totalPositionFields + totalBaseParamsFields) / instanceTables.length;
  
  console.log(`🔧 系统字段总数: ${totalSystemFields} 个 (平均每表 ${(totalSystemFields / instanceTables.length).toFixed(1)} 个)`);
  console.log(`📝 命名字段总数: ${totalNamingFields} 个 (平均每表 ${(totalNamingFields / instanceTables.length).toFixed(1)} 个)`);
  console.log(`📍 位置字段总数: ${totalPositionFields} 个 (平均每表 ${(totalPositionFields / instanceTables.length).toFixed(1)} 个)`);
  console.log(`⚙️  参数字段总数: ${totalBaseParamsFields} 个 (平均每表 ${(totalBaseParamsFields / instanceTables.length).toFixed(1)} 个)`);
  console.log(`📋 平均每表字段数: ${avgFieldsPerTable.toFixed(1)} 个`);
  
  // 5. 映射准确性分析
  console.log('\n🎯 映射准确性分析:');
  console.log('==================');
  
  // 命名字段准确性
  const namingAccuracy = (correctNamingTables.length / instanceTables.length) * 100;
  console.log(`📝 命名字段映射准确率: ${namingAccuracy.toFixed(1)}%`);
  
  // 检查是否所有表都有预期的系统字段
  const expectedSystemFields = 7; // id, device_type_code, instance_number, object_number, object_description, created_at, updated_at
  const correctSystemFieldTables = fieldAnalysis.filter(t => t.systemFields === expectedSystemFields);
  const systemFieldAccuracy = (correctSystemFieldTables.length / instanceTables.length) * 100;
  console.log(`🔧 系统字段完整率: ${systemFieldAccuracy.toFixed(1)}%`);
  
  // 6. 问题表识别
  console.log('\n🚨 问题表识别:');
  console.log('==============');
  
  const problemTables = fieldAnalysis.filter(t => 
    !t.hasCorrectNaming || t.systemFields !== expectedSystemFields
  );
  
  if (problemTables.length === 0) {
    console.log('🎉 所有表的映射都正确！');
  } else {
    console.log(`⚠️  发现 ${problemTables.length} 个问题表:`);
    problemTables.slice(0, 10).forEach(t => {
      const issues = [];
      if (!t.hasCorrectNaming) issues.push(`命名字段数量错误(${t.namingFields}/${coreFieldCounts['table_c2']})`);
      if (t.systemFields !== expectedSystemFields) issues.push(`系统字段数量错误(${t.systemFields}/${expectedSystemFields})`);
      
      console.log(`  - ${t.tableName}: ${issues.join(', ')}`);
    });
    
    if (problemTables.length > 10) {
      console.log(`  ... 还有 ${problemTables.length - 10} 个问题表`);
    }
  }
  
  // 7. 映射逻辑总结
  console.log('\n💡 映射逻辑总结:');
  console.log('================');
  
  console.log('📋 核心映射规则:');
  console.log('  1. 系统字段: 7个固定字段 (id, device_type_code, instance_number, object_number, object_description, created_at, updated_at)');
  console.log(`  2. 命名字段: table_c2 的 ${coreFieldCounts['table_c2']} 个字段 -> naming_字段名`);
  console.log(`  3. 位置字段: table_c4 的 ${coreFieldCounts['table_c4']} 个字段 或 table_c6 的 ${coreFieldCounts['table_c6']} 个字段 -> position_字段名`);
  console.log('  4. 参数字段: 对应的 table_c* 表的字段 -> base_params_字段名');
  console.log('  5. 数据类型: 所有字段统一为 TEXT 类型');
  
  console.log('\n🔗 映射关系确定逻辑:');
  console.log('  1. 通过 device_type_mappings 表的 english_name 匹配实例表名');
  console.log('  2. 获取 naming_table, position_table, base_params_table 三个属性表');
  console.log('  3. 将三个属性表的字段通过前缀命名整合到实例表中');
  console.log('  4. 替换原有的 record_id 外键关联方式');
  
  // 8. 验证结论
  console.log('\n🎉 验证结论:');
  console.log('============');
  
  if (namingAccuracy >= 95 && systemFieldAccuracy >= 95) {
    console.log('✅ 优秀！实例表字段映射基本准确无误');
    console.log('💡 映射逻辑正确，三个参数表的字段已成功整合到实例表中');
    console.log('🔧 系统现在使用整合式结构，无需外键关联');
  } else if (namingAccuracy >= 80 && systemFieldAccuracy >= 80) {
    console.log('⚠️  良好，大部分映射正确，存在少量问题需要修复');
  } else {
    console.log('❌ 映射存在较多问题，需要重新检查映射逻辑');
  }
  
  console.log(`\n📊 总体评估:`);
  console.log(`  命名字段映射准确率: ${namingAccuracy.toFixed(1)}%`);
  console.log(`  系统字段完整率: ${systemFieldAccuracy.toFixed(1)}%`);
  console.log(`  问题表数量: ${problemTables.length}/${instanceTables.length}`);
  
  db.close();
  
} catch (error) {
  console.error('❌ 验证失败:', error.message);
  console.error(error.stack);
}